﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using System.Data;
using Microsoft.VisualBasic;

namespace Simple.Services;

public class QHSEEarlyWarningService
{
    private readonly SimpleDbContext _context;
    public QHSEEarlyWarningService(SimpleDbContext context)
    {
        _context = context;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<PageResultModel<QHSEEarlyWarningDepartmentViolationResultModel>> GetDepartmentViolationEarlyWarningAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEEarlyWarningDepartmentViolationResultModel>();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .GroupBy(danger => danger.Violation.OrganizationName)
                         .Select(group => new QHSEEarlyWarningDepartmentViolationResultModel
                         {
                             OrganizationId = group.FirstOrDefault().Organization.OrganizationId,
                             TimeEnd = input.TimeEnd,
                             TimeOn = input.TimeOn,
                             OrganizationName = group.FirstOrDefault().Organization.NamePath,
                             MajorViolationsNumber = group.Count(violation => violation.Violation.ViolationType.Contains("重大"))
                         })
                        .Where(u => u.MajorViolationsNumber > 0)
                        .OrderBy(u => u.MajorViolationsNumber);
            // 获取总数量
            var totalRows = await newQuery.CountAsync();
            var models = await newQuery.Page(input.PageNo, input.PageSize).ToListAsync();
            result = new PageResultModel<QHSEEarlyWarningDepartmentViolationResultModel>
            {
                TotalRows = totalRows,
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return result;
        }
        return new PageResultModel<QHSEEarlyWarningDepartmentViolationResultModel>();
    }
    public async Task<PageResultModel<QHSEEarlyWarningDepartmentResultModel>> GetDepartmentDangerEarlyWarningAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEEarlyWarningDepartmentResultModel>();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var newQuery = query.Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                          .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                          .Where(u => u.Danger.Status == "未整改")
                         .GroupBy(danger => danger.Danger.OrganizationName)
                         .Select(group => new QHSEEarlyWarningDepartmentResultModel
                         {
                             OrganizationId = group.FirstOrDefault().Organization.OrganizationId,
                             TimeEnd = input.TimeEnd,
                             TimeOn = input.TimeOn,
                             OrganizationName = group.FirstOrDefault().Organization.NamePath,
                             MajorHazardsNumber = group.Count(danger => danger.Danger.DangerType == "重大隐患"),
                             MoreHazardsNumber = group.Count(danger => danger.Danger.DangerType == "较大隐患"),
                         })
                        .Where(u => u.MajorHazardsNumber >= 1 || u.MoreHazardsNumber >= 2)
                        .OrderBy(u => u.MajorHazardsNumber);
            // 获取总数量
            var totalRows = await newQuery.CountAsync();
            var models = await newQuery.Page(input.PageNo, input.PageSize).ToListAsync();
            result = new PageResultModel<QHSEEarlyWarningDepartmentResultModel>
            {
                TotalRows = totalRows,
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return result;
        }
        return new PageResultModel<QHSEEarlyWarningDepartmentResultModel>();
    }
    public async Task<PageResultModel<QHSEEarlyWarningPersonResultModel>> GetPersonEarlyWarningAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEEarlyWarningPersonResultModel>();

        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null || input.Score != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            if (input.CheckTypes.Count != 0)
            {
                query = query.Where(u => input.CheckTypes.Any(t => t == u.Check_Type));
            }
            var personScoresQuery = query
                .Join(_context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization });

            if (!string.IsNullOrEmpty(escapedNamePath))
            {
                personScoresQuery = personScoresQuery
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedNamePath}%", "/"));
            }
            var groupedQuery = personScoresQuery
                .GroupBy(u => new { u.Violation.ViolationUserName, u.Violation.ViolationUserId, u.Violation.ViolationUserDepart })
                .Select(group => new QHSEEarlyWarningPersonResultModel
                {
                    ViolationUserId = group.Key.ViolationUserId,
                    Score = group.Sum(violation => violation.Violation.Score),
                    CheckTime = group.Min(violation => violation.Violation.CheckDate),
                    ViolationUserName = group.Key.ViolationUserName,
                    OrganizationName = group.Min(violation => violation.Organization.NamePath) // Assuming Organization has a NamePath property
                })
                .Where(u => u.Score >= input.Score);

            var totalRows = await groupedQuery.CountAsync();
            var models = await groupedQuery
                .OrderByDescending(u => u.Score)
                .Skip((input.PageNo - 1) * input.PageSize)
                .Take(input.PageSize)
                .ToListAsync();

            result = new PageResultModel<QHSEEarlyWarningPersonResultModel>
            {
                TotalRows = totalRows,
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };

            return result;
        }

        return result;
    }



    public async Task<QHSEEarlyWarningNoticeResultModel> GetEarlyWarningNoticeAsync(IdInputModel input)
    {
        var organization = await _context.Set<QHSESysOrganization>().FindAsync(input.Id);
        var result = new QHSEEarlyWarningNoticeResultModel();
        if (string.IsNullOrEmpty(input.Id))
        {
            return result;
        }
        result.OrganizationId = input.Id;
        result.OrganizationName = organization.OrganizationName;
        result.TimeOn = new DateTime(DateTime.Now.Year, 1, 1);
        result.TimeEnd = DateTime.Now;
        result.Score = 6;
        var model = new QHSEEarlyWarningInputModel();
        model.OrganizationId = input.Id;
        model.OrganizationName = result.OrganizationName;
        model.TimeEnd = result.TimeEnd;
        model.Score = result.Score;
        model.TimeOn = result.TimeOn;
        result.WarningPersonnel = await GetPersonEarlyWarningCountAsync(model);
        result.WarningDepartments = await GetDepartmentDangerEarlyWarningCountAsync(model);
        result.WarningViolationDepartments = await GetDepartmentViolationEarlyWarningCountAsync(model);
        return result;

    }
    public async Task<int> GetPersonEarlyWarningCountAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null || input.Score != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);

            var personScoresQuery = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization }).AsQueryable();
            var personScores2 = await personScoresQuery
                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .GroupBy(u =>u.Violation.ViolationUserName+ u.Violation.ViolationUserId+u.Violation.ViolationUserDepart)
                .Select(group => new QHSEEarlyWarningPersonResultModel
                {
                    ViolationUserId = group.FirstOrDefault().Violation.ViolationUserId,
                    Score = group.Sum(violation => violation.Violation.Score),
                    CheckTime = group.FirstOrDefault().Violation.CheckDate,
                    ViolationUserName = group.FirstOrDefault().Violation.ViolationUserName,
                    OrganizationName = group.FirstOrDefault().Organization.NamePath // Assuming Organization has a Name property
                })
                .CountAsync(u => input.Score == null || u.Score >= input.Score);
            var personScores = personScores2;
            return personScores;
        }

        return 0;
    }
    public async Task<int> GetDepartmentDangerEarlyWarningCountAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FindAsync(input.OrganizationId);
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var danger = await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Where(u => u.Danger.Status == "未整改")
                         .GroupBy(danger => danger.Danger.OrganizationName)
                         .Select(group => new QHSEEarlyWarningDepartmentResultModel
                         {
                             TimeEnd = input.TimeEnd,
                             TimeOn = input.TimeOn,
                             OrganizationName = group.FirstOrDefault().Organization.NamePath,
                             MajorHazardsNumber = group.Count(danger => danger.Danger.DangerType == "重大隐患"),
                             MoreHazardsNumber = group.Count(danger => danger.Danger.DangerType == "较大隐患"),
                         })
                        .CountAsync(u => u.MajorHazardsNumber >= 1 || u.MoreHazardsNumber >= 2);
            return danger;
        }
        return 0;
    }
    public async Task<PageResultModel<QHSEDangerModel>> GetDepartmentDangerEarlyWarningListAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEDangerModel>();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath =
                EscapeLikePattern(org.NamePath);
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            danger => danger.OrganizationId,
                            organization => organization.OrganizationId,
                            (danger, organization) => new { Danger = danger, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                        .Where(u => u.Danger.Status == "未整改")
                         .Where(u => u.Danger.DangerType == "重大隐患" || u.Danger.DangerType == "较大隐患")
                         .Select(u => u.Danger)
                        .OrderBy(u => u.DangerType);
            // 获取总数量
            var totalRows = await newQuery.CountAsync();
            var dangers = await newQuery.Page(input.PageNo, input.PageSize).ToListAsync();
            var models = MapperHelper.Map<List<QHSEDangerModel>>(dangers);
            result = new PageResultModel<QHSEDangerModel>
            {
                TotalRows = totalRows,
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return result;
        }
        return result;
    }
    public async Task<PageResultModel<QHSEXyViolationModel>> GetDepartmentViolationEarlyWarningListAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEXyViolationModel>();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var newQuery = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Where(u => u.Violation.ViolationType.Contains("重大"))
                         .Select(u => u.Violation)
                        .OrderBy(u => u.ViolationType);
            // 获取总数量
            var totalRows = await newQuery.CountAsync();
            var dangers = await newQuery.Page(input.PageNo, input.PageSize).ToListAsync();
            var models = MapperHelper.Map<List<QHSEXyViolationModel>>(dangers);
            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = totalRows,
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return result;
        }
        return result;
    }
    public async Task<int> GetDepartmentViolationEarlyWarningCountAsync(QHSEEarlyWarningInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var violation = await query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                            violation => violation.OrganizationId,
                            organization => organization.OrganizationId,
                            (violation, organization) => new { Violation = violation, Organization = organization })
                        .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .GroupBy(danger => danger.Violation.OrganizationName)
                         .Select(group => new QHSEEarlyWarningDepartmentViolationResultModel
                         {
                             TimeEnd = input.TimeEnd,
                             TimeOn = input.TimeOn,
                             OrganizationName = group.FirstOrDefault().Organization.NamePath,
                             MajorViolationsNumber = group.Count(violation => violation.Violation.ViolationType.Contains("重大"))
                         })
                        .CountAsync(u => u.MajorViolationsNumber > 0);
            return violation;
        }
        return 0;
    }

    public async Task<PageResultModel<QHSEXyViolationModel>> GetEarlyWarningPersonListAsync(QHSEEarlyWarningPersonListInputModel input)
    {
        var query = _context.Set<QHSESysXyViolation>().AsNoTracking().AsQueryable();
        var result = new PageResultModel<QHSEXyViolationModel>();
        if (!string.IsNullOrEmpty(input.ViolationUserId) || input.TimeOn != null || input.TimeEnd != null)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = "";
            if (org != null)
            {
                escapedNamePath = EscapeLikePattern(org.NamePath);
            }
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd).OrderByDescending(u => u.Score);
            if (input.CheckTypes.Count != 0)
            {
                query = query.Where(u => input.CheckTypes.Any(t => t == u.Check_Type));
            }
            var personScoresQuery = query
                .Join(_context.Set<QHSESysOrganization>(), // Join with Organization table
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                    .AsQueryable();
            var persons = personScoresQuery.Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Where(u => u.Violation.ViolationUserName + u.Violation.ViolationUserId + u.Violation.ViolationUserDepart== input.ViolationUserName + input.ViolationUserId + u.Violation.ViolationUserDepart).Select(u=>u.Violation);
            // 获取总数量
            
            var violations = await persons.Page(input.PageNo, input.PageSize).ToListAsync();
            var totalRows = await persons.ToListAsync();
            var models = MapperHelper.Map<List<QHSEXyViolationModel>>(violations);
            result = new PageResultModel<QHSEXyViolationModel>
            {
                TotalRows = totalRows.Count(),
                Rows = models,
                PageNo = input.PageNo,
                PageSize = input.PageSize
            };
            return result;
        }
        return result;
    }
}
